﻿using Dapper;
using DataServer.Entities;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace DataServer.MySql
{
    public class SystemMag
    {
        private IDbConnection dbConnection;

        private IDbConnection DbConnection
        {
            get
            {
                dbConnection = new MySqlConnection(ConstConn.conn);

                bool isClosed = dbConnection.State == ConnectionState.Closed;
                if (isClosed) dbConnection.Open();
                return dbConnection;
            }
        }

        DapperEx dapperex = new DapperEx();
        public UserProfile GetUserbyUseridPwd(String userid, String pwd)
        {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT * FROM userprofile u where u.UserId=?UserId and u.pwd=?pwd and u.isactive=1 ");
                UserProfile param = new UserProfile();
                param.UserId = userid;
                param.Pwd = pwd;
                UserProfile useros = dapperex.FirstOrDefault<UserProfile>(sb.ToString(), param);
                return useros;
        }

        public UserProfile GetUserbyColumnName(String strcolumnname,String strvalue)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM userprofile u where u."+ strcolumnname + "=?"+ strcolumnname + " ");
            //UserProfile param = new UserProfile();
            //param.UserId = userid;
            //param.Pwd = pwd;
            UserProfile useros = new UserProfile();
            switch (strcolumnname)
            {
                case "UserId":
                    useros=dapperex.FirstOrDefault<UserProfile>(sb.ToString(), new { UserId = strvalue });
                    break;
                case "UserName":
                    useros = dapperex.FirstOrDefault<UserProfile>(sb.ToString(), new { UserName = strvalue });
                    break;
                case "Email":
                    useros = dapperex.FirstOrDefault<UserProfile>(sb.ToString(), new { Email = strvalue });
                    break;
                case "Telephone":
                    useros = dapperex.FirstOrDefault<UserProfile>(sb.ToString(), new { Telephone = strvalue });
                    break;
                default:
                    break;
            }
            
            return useros;
        }

        public UserProfile GetUserbyColumnName(String strcolumnname, String strvalue,String userid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM userprofile u where u." + strcolumnname + "=?" + strcolumnname + " and u.UserId!=?UserId ");
            //UserProfile param = new UserProfile();
            //param.UserId = userid;
            //param.Pwd = pwd;
            UserProfile useros = new UserProfile();
            switch (strcolumnname)
            {
                //case "UserId":
                //    useros = dapperex.FirstOrDefault<UserProfile>(sb.ToString(), new { UserId = strvalue });
                //    break;
                //case "UserName":
                //    useros = dapperex.FirstOrDefault<UserProfile>(sb.ToString(), new { UserName = strvalue });
                //    break;
                case "Email":
                    useros = dapperex.FirstOrDefault<UserProfile>(sb.ToString(), new { UserId = userid, Email = strvalue });
                    break;
                case "Telephone":
                    useros = dapperex.FirstOrDefault<UserProfile>(sb.ToString(), new { UserId = userid, Telephone = strvalue });
                    break;
                default:
                    break;
            }

            return useros;
        }

        public Boolean AddUser(UserProfile user)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" insert into userprofile (UserId,UserName,Pwd,Email,Telephone,AuthGroup,LastPCName,LastPcMac,LastPcIp,LastLoginTimeKey,IsActive,UserType,AccuntId) values(?UserId,?UserName,?Pwd,?Email,?Telephone,?AuthGroup,?LastPCName,?LastPcMac,?LastPcIp,?LastLoginTimeKey,?IsActive,?UserType,?AccuntId) ");
            return dapperex.Execute(sb.ToString(), user);
        }
        public Boolean AddUserHis(UserHistory userhis)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" insert into userhistory( UserId,UserName,Email,Telephone,AuthGroup,LastPCName,LastPcMac,LastPcIp,LastLoginTimeKey,IsActive,UserType,AccuntId) values(?UserId,?UserName,?Email,?Telephone,?AuthGroup,?LastPCName,?LastPcMac,?LastPcIp,?LastLoginTimeKey,?IsActive ,?UserType,?AccuntId) ");
            return dapperex.Execute(sb.ToString(), userhis);
        }

        public IEnumerable<UserProfile> QueryUser(String userid, String username, String isactive, String usertype)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(String.Format(@" SELECT * FROM userprofile  u where u.UserId like concat(?UserId,'%') and u.username like  concat(?UserName,'%') and u.UserType=?UserType   "));
            UserProfile uq = new UserProfile();
            uq.UserId = userid;
            uq.UserName = username;
            if (isactive.Equals("on"))
            {
                sb.Append(" and u.IsActive =?IsActive ");
                uq.IsActive = "1";
            }
            
            uq.UserType = usertype;
            IEnumerable<UserProfile> lsuser = dapperex.Query<UserProfile>(sb.ToString(), uq);
            return lsuser;
        }


        public Boolean ModifyUsers(List<UserProfile> lsuser,String userid)
        {
            String strnotpwd = "update userprofile set UserName=UserName,Pwd=Pwd,Email=Email,Telephone=?Telephone,AuthGroup=?AuthGroup,LastPCName=?LastPCName,LastPcMac=?LastPcMac,LastPcIp=?LastPcIp,LastLoginTimeKey=?LastLoginTimeKey,IsActive=?IsActive where UserId=?UserId ";
            String strpwd = "update userprofile set UserName=UserName,Pwd=?Pwd,Email=Email,Telephone=?Telephone,AuthGroup=?AuthGroup,LastPCName=?LastPCName,LastPcMac=?LastPcMac,LastPcIp=?LastPcIp,LastLoginTimeKey=?LastLoginTimeKey,IsActive=?IsActive where UserId=?UserId ";
            String strhistory = "insert into userhistory (UserId,UserName,Email,Telephone,AuthGroup,LastPCName,LastPcMac,LastPcIp,LastLoginTimeKey,IsActive ) values(?UserId,?UserName,?Email,?Telephone,?AuthGroup,?LastPCName,?LastPcMac,?LastPcIp,?LastLoginTimeKey,?IsActive ) ";

            IDbTransaction tran = DbConnection.BeginTransaction();
            try
            {
                int result = 0;
                foreach (UserProfile user in lsuser)
                {
                    UserHistory userhis = new UserHistory();
                    userhis.UserId = user.UserId;
                    userhis.UserName = user.UserName;
                    userhis.Telephone = user.Telephone;
                    userhis.IsActive = user.IsActive;
                    userhis.Email = user.Email;
                    userhis.AuthGroup = user.AuthGroup;
                    userhis.LastPcIp = user.LastPCIp;
                    userhis.LastPcMac = "";
                    userhis.LastPCName = user.LastPCName;
                    userhis.LastLoginTimeKey = DateTime.Now.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond.ToString();
                    if (user.Pwd!=null&&user.Pwd.Length > 0)
                    {
                        result += DbConnection.Execute(strpwd, user);
                        
                    }
                    else
                    {
                        result += DbConnection.Execute(strnotpwd, user);
                    }
                    result += DbConnection.Execute(strhistory, userhis);

                }


                tran.Commit();
                return true;

               
            }
            catch (Exception ex)
            {
                
                tran.Rollback();
                return false;
            }
        }


        public Accunt GetAccuntbyColumnName(String strcolumnname, String strvalue)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM accunt u where u." + strcolumnname + "=?" + strcolumnname + " ");
            //UserProfile param = new UserProfile();
            //param.UserId = userid;
            //param.Pwd = pwd;
            Accunt accunt = new Accunt();
            switch (strcolumnname)
            {
                case "AccuntId":
                    accunt = dapperex.FirstOrDefault<Accunt>(sb.ToString(), new { AccuntId = strvalue });
                    break;
                case "AccuntName":
                    accunt = dapperex.FirstOrDefault<Accunt>(sb.ToString(), new { AccuntName = strvalue });
                    break;                
                default:
                    break;
            }

            return accunt;
        }
        public Accunt GetAccuntByIDPWD(String straccuntid, String actpwd)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM accunt u where u.AccuntId=?AccuntId and  u.AccuntPayPWD=?AccuntPayPWD  ");
            Accunt accunt = dapperex.FirstOrDefault<Accunt>(sb.ToString(), new { AccuntId = straccuntid, AccuntPayPWD= actpwd });
            return accunt;
        }
        public Boolean AddAccunt(Accunt accunt)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" insert into accunt (AccuntId,AccuntName,AccuntPayPWD,IsActive,LastModifyTime,LastModifyer) values(?AccuntId,?AccuntName,?AccuntPayPWD,?IsActive,?LastModifyTime,?LastModifyer) ");
            return dapperex.Execute(sb.ToString(), accunt);
        }

        public IEnumerable<Accunt> QueryAccunt(String accuntid, String accuntname, String isactive)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(String.Format(@" SELECT * FROM accunt  a where a.AccuntId like concat(?AccuntId,'%') and a.AccuntName like  concat(?AccuntName,'%')  "));
            Accunt ac = new Accunt();
            ac.AccuntId = accuntid;
            ac.AccuntName = accuntname;
            if (isactive.Equals("on"))
            {
                sb.Append(" and a.IsActive =?IsActive ");
                ac.IsActive = "1";
            }            
            IEnumerable<Accunt> lsaccunt = dapperex.Query<Accunt>(sb.ToString(), ac);
            return lsaccunt;
        }

        public Boolean ModifyAccunt(List<Accunt> lsaccunt, String userid)
        {
            String strnotpwd = "update accunt set AccuntName=?AccuntName,AccuntPayPWD=?AccuntPayPWD,IsActive=?IsActive,LastModifyTime=?LastModifyTime,LastModifyer=?LastModifyer  where AccuntId=?AccuntId ";
            String strpwd = "update accunt set AccuntName=?AccuntName,AccuntPayPWD=?AccuntPayPWD,IsActive=IsActive,LastModifyTime=?LastModifyTime,LastModifyer=?LastModifyer  where AccuntId=?AccuntId ";

            IDbTransaction tran = DbConnection.BeginTransaction();
            try
            {
                int result = 0;
                foreach (Accunt accunt in lsaccunt)
                {
                   
                    if (accunt.AccuntPayPWD != null && accunt.AccuntPayPWD.Length > 0)
                    {
                        result += DbConnection.Execute(strnotpwd, accunt);

                    }
                    else
                    {
                        result += DbConnection.Execute(strnotpwd, accunt);
                    }
                }


                tran.Commit();
                return true;


            }
            catch (Exception ex)
            {

                tran.Rollback();
                return false;
            }
        }
        public BankCardAccunt GetBankAccuntbyColumnName(String strcolumnname, String strvalue)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM bankcardaccunt u where u." + strcolumnname + "=?" + strcolumnname + " ");
            //UserProfile param = new UserProfile();
            //param.UserId = userid;
            //param.Pwd = pwd;
            BankCardAccunt accunt = new BankCardAccunt();
            switch (strcolumnname)
            {
                case "BankCardNo":
                    accunt = dapperex.FirstOrDefault<BankCardAccunt>(sb.ToString(), new { BankCardNo = strvalue });
                    break;
                default:
                    break;
            }

            return accunt;
        }


        public Boolean AddBankCardAccunt(BankCardAccunt bankaccunt, String userid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@" insert into bankcardaccunt (BankCardKey,BankCardNo,BankCardUserName,BankCardBankName,BankCardSubName,BankCardSafeCode,BankCardPayPWD,LastModifyer,LastModifyTime,AccuntId) 
                values(?BankCardKey,?BankCardNo,?BankCardUserName,?BankCardBankName,?BankCardSubName,?BankCardSafeCode,?BankCardPayPWD,?LastModifyer,?LastModifyTime,?AccuntId) ");
            return dapperex.Execute(sb.ToString(), bankaccunt);
        }


        public IEnumerable<UserProfile> GetUsersByAccuntID(String accuntid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(String.Format(@" SELECT * FROM userprofile  u where u.AccuntId=?AccuntId AND u.IsActive=?IsActive "));
            IEnumerable<UserProfile> lsuser = dapperex.Query<UserProfile>(sb.ToString(),new { AccuntId = accuntid , IsActive ="1"});
            return lsuser;
        }
        public IEnumerable<UserProfile> GetUsersNotSignAccunt()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(String.Format(@" SELECT * FROM userprofile  u where (u.AccuntId is null or u.AccuntId='' ) AND u.IsActive=?IsActive "));
            IEnumerable<UserProfile> lsuser = dapperex.Query<UserProfile>(sb.ToString(), new { IsActive = "1" });
            return lsuser;
        }

        public UserProfile GetUserbyUserid(String userid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM userprofile u where u.UserId=?UserId  ");
            UserProfile param = new UserProfile();
            param.UserId = userid;
            UserProfile useros = dapperex.FirstOrDefault<UserProfile>(sb.ToString(), param);
            return useros;
        }

        public Boolean UpdateUserAccunt(List<UserProfile> lsadd, List<UserProfile> lsdel, String userid)
        {
            String strsql = "update userprofile set AccuntId=?AccuntId,LastPCName=?LastPCName,LastPcMac=?LastPcMac,LastPcIp=?LastPcIp,LastLoginTimeKey=?LastLoginTimeKey where UserId=?UserId ";



            IDbTransaction tran = DbConnection.BeginTransaction();
            try
            {
                int result = 0;
                foreach (UserProfile user in lsadd)
                {
                    result += DbConnection.Execute(strsql, user);

                }

                foreach (UserProfile user in lsdel)
                {
                    result += DbConnection.Execute(strsql, user);

                }
                tran.Commit();
                return true;
            }
            catch
            {
                tran.Rollback();
                return false;
            }
        }
    }
}
